﻿Imports System.Data.OleDb
Public Class ResearchListDetails
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If IsPostBack = False Then
            If Request.Params("ID") = "" Then Response.Redirect("ResearchList.aspx")
            Dim likeConn As New OleDbConnection(ConfigurationManager.ConnectionStrings("FinalConnectionString").ConnectionString)
            likeConn.Open()
            Dim LikedSql = "SELECT * FROM Likes WHERE Research_fkID=@f1 AND Reader=@f2"
            Dim likeOrNotCmd As OleDbCommand = New OleDbCommand(LikedSql, likeConn)
            likeOrNotCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            likeOrNotCmd.Parameters.AddWithValue("@f2", User.Identity.Name)
            Dim drLike = likeOrNotCmd.ExecuteReader
            If drLike.HasRows Then
                btn_like.Visible = False
                btn_unLike.Visible = True
            Else
                btn_like.Visible = True
                btn_unLike.Visible = False
            End If
        End If

        'this is for showing Comments of a particular Research by using code behind directly when anyone submitted a comment without using submit button
        'If is used if there is no parameter for ID field, it goes to Research List Page
        'IsPostBack, it is only time that executes when it comes from different page
        If IsPostBack = False Then
            If Request.Params("ID") = "" Then Response.Redirect("ResearchList.aspx")
            Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("FinalConnectionString").ConnectionString)
            Dim sql = "SELECT * FROM Comments WHERE Research_fkID = @f1"
            Dim cmdDB = New OleDbCommand(sql, conn)
            cmdDB.Parameters.AddWithValue("@f1", Request.Params("ID"))
            conn.Open()
            Dim ExReader = cmdDB.ExecuteReader()
            GridView2.DataSource = ExReader
            GridView2.DataBind()

            'This is for Users that also viewed other researches 
            Dim viewSql As String = "INSERT INTO ResearchesAlsoViewed (Addedby, Research_fkID) VALUES (@f1, @f2)"
            Dim viewCmd As OleDbCommand = New OleDbCommand(viewSql, conn)
            viewCmd.CommandType = CommandType.Text
            viewCmd.Parameters.AddWithValue("@f1", User.Identity.Name)
            viewCmd.Parameters.AddWithValue("@f2", Request.Params("ID"))
            viewCmd.ExecuteNonQuery()
        End If

    End Sub


    Protected Sub btn_submitComment_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_submitComment.Click
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("FinalConnectionString").ConnectionString)
        Dim sql = "INSERT INTO Comments (Author,Comment,Title, Research_fkID) VALUES (@f1,@f2,@f3,@f4)"
        Dim cmdDB = New OleDbCommand(sql, conn)
        cmdDB.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmdDB.Parameters.AddWithValue("@f2", tb_commentPlaintext.Text)
        cmdDB.Parameters.AddWithValue("@f3", tb_commentTitle.Text)
        cmdDB.Parameters.AddWithValue("@f3", Request.Params("ID"))
        conn.Open()
        cmdDB.ExecuteNonQuery()
        Dim commFill As String = "SELECT * FROM Comments WHERE Research_fkID = @f1"
        Dim commFillCmd As OleDbCommand = New OleDbCommand(commFill, conn)
        commFillCmd.CommandType = CommandType.Text
        commFillCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        Dim ExReader = commFillCmd.ExecuteReader()
        GridView2.DataSource = ExReader
        GridView2.DataBind()
        tb_commentTitle.Text = ""
        tb_commentPlaintext.Text = ""
        conn.Close()

        If Not User.IsInRole("superuser") Then
            Dim superUserSql = "INSERT INTO aspnet_UsersInRoles (UserId, RoleId) VALUES (@f1, @f2)"
            Dim superUserCmd = New OleDbCommand(superUserSql, conn)
            conn.Open()
            superUserCmd.Parameters.AddWithValue("@f1", Membership.GetUser(User.Identity.Name).ProviderUserKey)
            superUserCmd.Parameters.AddWithValue("@f2", "1")
            superUserCmd.ExecuteNonQuery()
            conn.Close()
        End If

        'Response.Redirect("SubCommSucceeded.aspx")
    End Sub

    Protected Sub btn_tagResearch_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_tagResearch.Click
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("FinalConnectionString").ConnectionString)
        Dim sql = "INSERT INTO ResearchTagjunction (Research_fkID, Tag_fkID) VALUES (@f1,@f2)"
        conn.Open()
        For Each chkBox As ListItem In cbl_Tags.Items
            If chkBox.Selected Then
                Dim cmdDB = New OleDbCommand(sql, conn)
                cmdDB.CommandType = CommandType.Text
                cmdDB.Parameters.AddWithValue("@f1", Request.Params("ID"))
                cmdDB.Parameters.AddWithValue("@f2", chkBox.Value)
                cmdDB.ExecuteNonQuery()
            End If
        Next
        'this is for adding new tag to Tag table
        'seperate new tags by comma, if they are more than one
        'newTags is an array
        'newTag is temporary for each one of the tags
        'If used for checking that if a user added nothing to Tag table, it will return nothing
        If tb_addNewTag.Text = "" Then Return
        Dim newTags = tb_addNewTag.Text.Split(",")
        For Each newTag In newTags
            Dim newTagsql As String = "INSERT INTO Tags (Tag) VALUES (@f1)"
            Dim cmdDB As OleDbCommand = New OleDbCommand(newTagsql, conn)
            cmdDB.CommandType = CommandType.Text
            cmdDB.Parameters.AddWithValue("@f1", newTag)
            cmdDB.ExecuteNonQuery()

            'finding the new tag ID
            Dim lastIDcmd As OleDbCommand = New OleDbCommand("SELECT TOP 1 ID FROM Tags ORDER BY ID DESC", conn)
            Dim dr = lastIDcmd.ExecuteReader()
            dr.Read()
            Dim lastID = dr.GetValue(0)

            'associate the tag ID with the research ID
            Dim newCmd As OleDbCommand = New OleDbCommand(sql, conn)
            newCmd.CommandType = CommandType.Text
            newCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            newCmd.Parameters.AddWithValue("@f2", lastID)
            newCmd.ExecuteNonQuery()
        Next
    End Sub

    Protected Sub btn_addTag_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_addTag.Click
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("FinalConnectionString").ConnectionString)
        Dim sql = "INSERT INTO ResearchTagjunction (Research_fkID, Tag_fkID) VALUES (@f1,@f2)"
        conn.Open()
        For Each chkBox As ListItem In cbl_Tags.Items
            If chkBox.Selected Then
                Dim cmdDB = New OleDbCommand(sql, conn)
                cmdDB.CommandType = CommandType.Text
                cmdDB.Parameters.AddWithValue("@f1", Request.Params("ID"))
                cmdDB.Parameters.AddWithValue("@f2", chkBox.Value)
                cmdDB.ExecuteNonQuery()
            End If
        Next
    End Sub

    Protected Sub btn_researchRating_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_researchRating.Click
        Dim conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("FinalConnectionString").ConnectionString)
        Dim delSql = "DELETE FROM ResearchRating WHERE AddedBy=@f1 AND Research_fkID=@f2"
        Dim delCmd As New OleDbCommand(delSql, conn)
        conn.Open()
        delCmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        delCmd.Parameters.AddWithValue("@f2", Request.Params("ID"))
        delCmd.ExecuteNonQuery()

       

        Dim addSql = "INSERT INTO ResearchRating (Research_fkID,AddedBy,Rating) VALUES (@f1,@f2,@f3)"
        Dim addCmd As New OleDbCommand(addSql, conn)
        addCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        addCmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        addCmd.Parameters.AddWithValue("@f3", r_Rating.CurrentRating)
        addCmd.ExecuteNonQuery()

        Dim updtResearchSql = "UPDATE Researches SET Rating=@f1 WHERE AddedBy=@f2 AND ID=@f3"
        Dim updtResearchCmd As New OleDbCommand(updtResearchSql, conn)
        updtResearchCmd.Parameters.AddWithValue("@f1", r_Rating.CurrentRating)
        updtResearchCmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        updtResearchCmd.Parameters.AddWithValue("@f3", Request.Params("ID"))
        updtResearchCmd.ExecuteNonQuery()

    End Sub

    Protected Sub btn_like_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_like.Click
        Dim conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("FinalConnectionString").ConnectionString)
        Dim likeSql = "INSERT INTO Likes (Research_fkID, Reader) VALUES (@f1,@f2)"
        Dim likeCmd As OleDbCommand = New OleDbCommand(likeSql, conn)
        likeCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        likeCmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        conn.Open()
        likeCmd.ExecuteNonQuery()
        btn_like.Visible = False
        btn_unLike.Visible = True
        conn.Close()
        likeCmd.Dispose()
        conn.Dispose()

    End Sub

    Protected Sub btn_unLike_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_unLike.Click
        Dim conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("FinalConnectionString").ConnectionString)
        Dim unLikeSql = "DELETE FROM Likes WHERE Research_fkID= @f1 AND Reader=@f2"
        Dim unLikeCmd As OleDbCommand = New OleDbCommand(unLikeSql, conn)
        unLikeCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        unLikeCmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        conn.Open()
        unLikeCmd.ExecuteNonQuery()
        btn_unLike.Visible = False
        btn_like.Visible = True
        conn.Close()
        unLikeCmd.Dispose()
        conn.Dispose()


    End Sub
End Class